Sorts

Quintile returns

• At beginning of each time period, sort stocks into quintiles based on a predictor.
• Could be deciles instead, or …
• Time periods could be days, weeks, months, … Days would be realistic. Months is easier.
• Each quintile is a portfolio. Compute return for the period.
  
• Repeat next period.

Data

• Monthly data in SQL database
• 100+ predictors described in ghz-predictors.xlsx
• Today, sort on individual predictors
• Next topic: combine predictors via machine learning

SQL

• select [columns] from [table]
• join [another table] on [variables to match on]
• where [select rows based on conditions]
• order by [columns to sort on]

Connect with python

from sqlalchemy import create_engine
import pymssql
import pandas as pd

server = "mssql-82792-0.cloudclusters.net:16272"
username = "user"
password = "" # paste password between quote marks
database = "ghz"
string = "mssql+pymssql://" + username + ":" + password + "@" + server + "/" + database
conn = create_engine(string).connect()

Example: ROEQ

data = pd.read_sql(
    """
    select ticker, date, ret, roeq
    from data
    order by date
    """, 
    conn
)
data = data.dropna()
data.head()

Sort into quintiles each month

data["quintile"] = data.groupby("date").roeq.transform(
  lambda x: pd.qcut(x, 5, labels=range(1, 6))
)
data.head()

Compute portfolio returns each month

rets = data.groupby(['date', 'decile']).ret.mean()
rets = rets.unstack()
rets.head()

Average returns across months

rets.mean()

decile
1    0.010083
2    0.011746
3    0.012197
4    0.012553
5    0.012682
dtype: float64

Compound returns across months

(1+rets).cumprod().plot()